#!/usr/bin/env python
# encoding: utf-8
"""
@Author: Beam
@Mail:506556658@qq.com
@file: models.py
@time: 2017/6/22 15:38
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base  #orm基类方法
from sqlalchemy import Column, Integer, String, ForeignKey,Enum,UniqueConstraint ,Table  #导入字段方法
from sqlalchemy.orm import sessionmaker,relationship  #从orm中导入方法
from sqlalchemy_utils import ChoiceType

Base = declarative_base()  # 生成orm基类，用import的方法生成基类

# #建立多对多关系
user_m2m_bindhost = Table('user_m2m_bindhost',Base.metadata,  #Table需要导入
                             Column('userprofile_id',Integer,ForeignKey('user_profile.id')),
                             Column('bindhost_id',Integer,ForeignKey('bind_host.id')),
                    )

bindhost_m2m_hostgroup = Table('bindhost_m2m_hostgroup',Base.metadata,  #Table需要导入
                             Column('bindhost_id',Integer,ForeignKey('bind_host.id')),
                             Column('hostgroup_id',Integer,ForeignKey('host_group.id')),
                    )

userprofile_m2m_hostgroup = Table('userprofile_m2m_hostgroup',Base.metadata,  #Table需要导入
                             Column('userprofile_id',Integer,ForeignKey('user_profile.id')),
                             Column('hostgroup_id',Integer,ForeignKey('host_group.id')),
                    )

class Host(Base):
    '''主机信息表'''
    __tablename__ = 'host'
    id = Column(Integer,primary_key=True)
    hostname = Column(String(64),unique=True)  #唯一字段
    ip = Column(String(32),unique=True)
    port = Column(Integer,default=2)  #字段默认值
    # remote_user = relationship('RemoteUser',secondary=host_m2m_remoteuser,backref='hosts')  #关联到remote_user表

    def __repr__(self):
        return self.hostname

class HostGroup(Base):
    '''主机组'''
    __tablename__ = 'host_group'
    id = Column(Integer, primary_key=True)
    hostname = Column(String(64), unique=True)  # 唯一字段
    bind_hosts = relationship("BindHost",secondary="bindhost_m2m_hostgroup",backref="host_groups")
    def __repr__(self):
        return self.hostname

class RemoteUser(Base):
    '''主机的账号密码'''
    __tablename__ = 'remote_user'
    __table_args__ = (UniqueConstraint('auth_type','username','password',name='_user_passwd_uc'),)  #需要导入UniqueConstraint,创建3个字段联合唯一
    id = Column(Integer, primary_key=True)
    AuthTypes = [('ssh-passwd','SSH/Password'), #前面的是数据库真是的，后面的是显示给我们看的，可以携程中文
                 ('ssh-key','SSH/KEY'),]
    auth_type = Column(ChoiceType(AuthTypes)) #choicetype需要从SQLAlchemy-Utils导入方法
    username = Column(String(32),unique=True)
    password = Column(String(128),unique=True)
    def __repr__(self):
        return self.username

class BindHost(Base):
    '''hostip  remoteusername hostgroupname'''
    __table__ = 'bind_host'
    __table_args__ = (UniqueConstraint('host_id', 'remoteuser_id', name='_host_group_remoteuser_uc'),)
    id = Column(Integer, primary_key=True)
    host_id = Column(Integer,ForeignKey('host.id'))
    #group_id = Column(Integer,ForeignKey('host_group.id'))
    remoteuser_id = Column(Integer,ForeignKey('remote_user.id'))
    host = relationship("Host",backref="bind_host")
    #host_group = relationship("Group", backref="bind_host")
    remote_user = relationship("RemoteUser",backref="bind_host")
    def __repr__(self):
        return "<%s -- %s -- %s>" % (self.host.ip ,self.remote_user.username)


class UserProfile(Base):
    '''堡垒机自身用户登录的账号密码'''
    __tablename__ = 'user_profile'
    id = Column(Integer, primary_key=True)
    username= Column(String(32),unique=True)
    password = Column(String(128))
    bind_hosts = relationship("BindHost",secondary="user_m2m_bindhost",backref="user_profile")
    bind_host_groups = relationship("HostGroup",secondary="userprofile_m2m_hostgroup",backref="user_profile")  #先写类，再写多对多的关系，再写可以查那个表的字段

    def __repr__(self):
        return self.username

class AuditLog(Base):
    '''用户执行的命令'''
    __tablename__ = 'audit_log'
    id = Column(Integer, primary_key=True)

